<?php
class m_public_function extends CI_Model {
	
	function _menus(){
		if ($_SESSION[SESSION_GROUPID] == -1){
		$sql ="select child.*, parent.menus_label as parent_label  from s_menus as child
				left join s_menus as parent ON child.menus_parent = parent.menus_id
				where child.menus_active =  'y'
				and child.menus_parent <> -1
				order by parent.menus_seq , parent.menus_seq, child.menus_seq";
		}else{
		$sql = "select child.*, parent.menus_label as parent_label from s_role 
				left join s_menus as child on child.menus_id = s_menus_id 
				left join s_menus as parent ON child.menus_parent = parent.menus_id
				where s_groups_id =  '".$_SESSION[SESSION_GROUPID]."'
				and child.menus_active =  'y'
				and child.menus_parent <> -1
				order by   parent.menus_seq, child.menus_seq ";
		}
		$query = $this->db->query($sql);
		$result = array();
		foreach($query->result() as $row){
			$result[$row->parent_label][] = $row;
		}
		return $result;
	}
	
	function _main_menus(){
		if ($_SESSION[SESSION_GROUPID] == -1){
		$sql ="select parent.* from
				s_menus as node 
				left join s_menus as parent on parent.menus_id = node.menus_parent
				and node.menus_active =  'y'
				where node.menus_parent <> -1
				group by node.menus_parent
				order by parent.menus_seq";
		}else{
		$sql = "select parent.* from s_role 
				left join s_menus as node on node.menus_id = s_menus_id
				left join s_menus as parent on parent.menus_id = node.menus_parent
				where s_groups_id = '".$_SESSION[SESSION_GROUPID]."'
				and node.menus_active =  'y'
				group by node.menus_parent
				order by parent.menus_seq";
		}
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	
	/* all data */
	
    function _flow(){
		$sql = "SELECT flow_id, flow_code, flow_name FROM m_flow";
		$query = $this->db->query($sql);
		return $query->result();
    }
    
    function _getdocumentno($flow_id, $project_id){
        $sql = "SELECT if(flow_subcode is null, '', concat(flow_subcode,'-', count(*))) seq 
        FROM t_document 
        LEFT JOIN m_flow ON flow_id = m_flow_id WHERE t_project_id = '".$project_id."'
            and m_flow_id = '".$flow_id."'
        ";
		$query = $this->db->query($sql);
        return $query->row("seq");    
    }
    
	function _project(){
		$sql = "SELECT project_id, project_name, project_code FROM t_project";
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	function _project2(){
		$sql = "SELECT project_id, project_name, project_code FROM t_project
        LEFT JOIN t_userproject ON t_userproject.t_project_id = project_id
        WHERE 1=1 ";
        
        if (strtolower($_SESSION[SESSION_GROUPNAMA]) == "cabang" )
             $sql .=    "  AND t_project.m_cabang_id = ".$_SESSION[SESSION_CABANGID]." ";
         else if (strtolower($_SESSION[SESSION_GROUPNAMA]) == "proyek" )
             $sql .=    "  AND t_userproject.s_users_id = '".$_SESSION[SESSION_USERID]."' ";

             
		$query = $this->db->query($sql);
		return $query->result();
	}
    
	function _divbyuser($user_id){
		$sql = "SELECT DISTINCT div_id, div_name FROM `m_flowdet`
		left join m_div on m_div_id =  div_id
		left join s_user on s_user.m_groupdiv_id =  s_users_id
		WHERE user_id = '".mysql_real_escape_string($user_id)."'";
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	function _flowdetfortable($flow_id){
		$sql = "select div_id, div_name,
				if (flowdet_id is null, div_id, null) notused_id,
				if (flowdet_id is not null, div_id, null) used_id,
				if (flowdet_id is null, div_name, null) notused_name,
				if (flowdet_id is not null, div_name, null) used_name,
				s_users_id from
				 m_div
				left join  ( select * from m_flowdet where m_flow_id = '".mysql_real_escape_string($flow_id)."' and flowdet_active = 'y') a
				on m_div_id = div_id
				order by flowdet_seq ASC
			";
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	function _checklistbyflowdet($flowdet_id,$document_id,$is_last,$last){
		if ($is_last){
			$sql = " 
			(select DISTINCT flowdetchecklist_id, concat(div_name,' : ', checklist_name) as checklist_name from t_trackingdet left join  
				t_tracking on t_tracking_id = tracking_id
				left join m_flowdetchecklist on m_flowdetchecklist_id = flowdetchecklist_id 
				left join m_flowdet on m_flowdet_id = flowdet_id 
				left join m_div on m_div_id = div_id 
				left join m_checklist on m_checklist_id =checklist_id 
				where t_document_id = '".($document_id)."'
				and trackingdet_flag = 2
				and div_id <> '".$last."'
				order by div_id )
			UNION ALL
			(SELECT flowdetchecklist_id, checklist_name FROM m_flowdetchecklist
			left join m_checklist on m_checklist_id =  checklist_id
			WHERE m_flowdet_id = '".($flowdet_id)."' )";
		}else{
			$sql = "SELECT flowdetchecklist_id, checklist_name FROM m_flowdetchecklist
			left join m_checklist on m_checklist_id =  checklist_id
			WHERE m_flowdet_id = '".($flowdet_id)."'";
		
		}
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	function projectcodebyid($project_id){
		$sql = "SELECT project_code FROM t_project WHERE project_id = '".mysql_real_escape_string($project_id)."'";
		$query = $this->db->query($sql);
		$row = $query->row("project_code");
		return $row;
	}
    
    function flowbyuser($user_id){
        $sql = "SELECT flow_id,flow_name FROM `m_flowdet` 
LEFT JOIN m_flow ON m_flow_id = flow_id
where flowdet_seq = 1 and s_users_id = $user_id 
and flow_id in (1,2,4,5,6)";
		$query = $this->db->query($sql);
        // echo $sql;
		return $query->result();
    }
    
    function notifflow($flow){
        switch($flow){
            case 1 :
                $sql = "select 'Dokumen PBB' doc, project_name, project_code, project_date, document_noflow  from t_project LEFT JOIN t_document
ON t_project_id = project_id AND m_flow_id = 1
WHERE document_noflow is null and datediff(NOW(),project_date) >= 14";
                
            break;
            case 2 :
                $sql = "select 'Dokumen PDP' doc, project_name, project_code, project_date, document_noflow  from t_project LEFT JOIN t_document
ON t_project_id = project_id AND m_flow_id = 2
WHERE document_noflow is null and datediff(NOW(),project_date) >= 7";
                
            break;
            case 4 :
                $sql = "select  'Dokumen RP' doc,project_name, project_code, project_date, d1.document_noflow , d2.document_noflow  from t_project LEFT JOIN t_document d1
ON d1.t_project_id = project_id AND d1.m_flow_id = 1 
LEFT JOIN t_document d2
ON d2.t_project_id = project_id AND d2.m_flow_id = 4
WHERE d1.document_noflow is NOT null and d2.document_noflow is null
and datediff(NOW(),d1.document_insert_date) > 7";
                
            break;
            case 5 :
                $sql = "select  'Dokumen RAPK' doc, project_name, project_code, project_date, document_noflow  from t_project LEFT JOIN t_document
ON t_project_id = project_id AND m_flow_id = 5
WHERE document_noflow is null and datediff(NOW(),project_date) >= 60";
                
            break;
            case 6 :
                $sql = "select  'Dokumen AK' doc,project_name, project_code, project_date, document_noflow  from t_project LEFT JOIN t_document
ON t_project_id = project_id AND m_flow_id = 6
WHERE document_noflow is null and datediff(NOW(),project_date) >= 60";
                
            break;
        }
        
            $query = $this->db->query($sql);
            return $query->result();
    }
    
    /* data */
    
    function m_cabang(){
		$sql = "SELECT cabang_id, cabang_nama FROM m_cabang";
		$query = $this->db->query($sql);
		$data = array("");
		foreach($query->result() as $row){
			$data[$row->cabang_id] = $row->cabang_nama;
		}
		return $data;
    }
	
}